﻿using SqlSugar;
using Zhaoxi.SqlSugar.Common;
using Zhaoxi.SqlSugar.Models;

namespace Zhaoxi.SqlSugarAdvanced
{
    /// <summary>
    /// 分表
    /// </summary>
    public static class AutomaticSubTableDemo
    {
        /// <summary>
        /// 按年分表
        /// </summary>
        public static void ShowSubTableYear()
        {
            var connectionconfig = new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true
            };

            List<CommoditySubTableYear> addList = new List<CommoditySubTableYear>();
            for (int i = 1; i <= 10000; i++)
            {
                int seed = i % 10;
                addList.Add(new CommoditySubTableYear()
                {
                    CategoryId = i,
                    Createtime = DateTime.Now.AddYears(seed),
                    ImageUrl = "",
                    Price = new Random().Next(100, 1000),
                    ProductId = i,
                    Title = $"Title_{i}",
                    Url = $"Url_{i}",
                    Id = Guid.NewGuid()
                });
            }

            using (SqlSugarClient db = new SqlSugarClient(connectionconfig))
            {

                db.CodeFirst
                .SplitTables()//标识分表
                 .InitTables<CommoditySubTableYear>(); //程序启动时加这一行,如果一张表没有会初始化一张


                //插入数据
                {
                    db.Insertable<CommoditySubTableYear>(addList)
                        .SplitTable()//标识分表
                        .ExecuteCommand();
                }

                {
                    //清除所有的数据
                    DateTime beginDate = DateTime.Now.AddYears(-1);
                    DateTime endDate = DateTime.Now.AddYears(20);
                    var list = db.Queryable<CommoditySubTableYear>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    List<CommoditySubTableYear> delList = db.Queryable<CommoditySubTableYear>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    db.Deleteable(delList)
                        .SplitTable()//标识分表
                        .ExecuteCommand();
                }

                long iResult = db.Insertable(addList)
                     .SplitTable()//标识分表
                     .ExecuteCommand();


                {
                    //注意：
                    //1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序
                    //2、 Where尽量写到 SplitTable 前面，先过滤在union all 
                    //原理：(sql union sql2) 写SplitTable 后面生成的括号外面，写前生成的在括号里面

                    DateTime beginDate = DateTime.Now;
                    DateTime endDate = DateTime.Now.AddYears(2);
                    var list = db.Queryable<CommoditySubTableYear>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                }


                {
                    //近3张，也可以表达式选择
                    var list = db.Queryable<CommoditySubTableYear>()
                        .SplitTable(tabs => tabs.Take(3))//标识分表
                        .ToList();
                }
                //确定某一个表名查询
                {
                    var name = db.SplitHelper<CommoditySubTableYear>().GetTableName(DateTime.Now.AddYears(3));//根据时间获取表名
                    var list = db.Queryable<CommoditySubTableYear>()
                        .SplitTable(tas => tas.InTableNames(name))//标识分表
                        .ToList();
                }

                //表名模糊匹配
                {
                    var list = db.Queryable<CommoditySubTableYear>()
                            .SplitTable(tas => tas.Where(y => y.TableName.Contains("2022")))//表名包含2019的表
                            .ToList();
                }
            }
        }

        /// <summary>
        /// 按月分表
        /// </summary>
        public static void ShowSubTableMonth()
        {
            var connectionconfig = new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true
            };

            List<CommoditySubTableMonth> addList = new List<CommoditySubTableMonth>();
            for (int i = 1; i <= 10000; i++)
            {
                int seed = i % 12;
                addList.Add(new CommoditySubTableMonth()
                {
                    CategoryId = i,
                    Createtime = DateTime.Now.AddMonths(seed),
                    ImageUrl = "",
                    Price = new Random().Next(100, 1000),
                    ProductId = i,
                    Title = $"Title_{i}",
                    Url = $"Url_{i}",
                    Id = Guid.NewGuid()
                });
            }

            using (SqlSugarClient db = new SqlSugarClient(connectionconfig))
            {

                db.CodeFirst
                .SplitTables()//标识分表
                 .InitTables<CommoditySubTableMonth>(); //程序启动时加这一行,如果一张表没有会初始化一张

                long iResult = db.Insertable(addList)
                   .SplitTable()//标识分表
                   .ExecuteCommand();


                {
                    //清除所有的数据
                    DateTime beginDate = DateTime.Now;
                    DateTime endDate = DateTime.Now.AddMonths(20);
                    var list = db.Queryable<CommoditySubTableMonth>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    List<CommoditySubTableMonth> delList = db.Queryable<CommoditySubTableMonth>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    db.Deleteable(delList)
                        .SplitTable()//标识分表
                        .ExecuteCommand();
                }

              

                {
                    //注意：
                    //1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序
                    //2、 Where尽量写到 SplitTable 前面，先过滤在union all 
                    //原理：(sql union sql2) 写SplitTable 后面生成的括号外面，写前生成的在括号里面

                    DateTime beginDate = DateTime.Now;
                    DateTime endDate = DateTime.Now.AddMonths(20);
                    var list = db.Queryable<CommoditySubTableMonth>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                }


                {
                    //近3张，也可以表达式选择
                    var list = db.Queryable<CommoditySubTableMonth>()
                        .SplitTable(tabs => tabs.Take(3))//标识分表
                        .ToList();
                }
                //确定某一个表名查询
                {
                    var name = db.SplitHelper<CommoditySubTableMonth>().GetTableName(DateTime.Now.AddYears(3));//根据时间获取表名
                    var list = db.Queryable<CommoditySubTableMonth>()
                        .SplitTable(tas => tas.InTableNames(name))//标识分表
                        .ToList();
                }

                //表名模糊匹配
                {
                    var list = db.Queryable<CommoditySubTableMonth>()
                            .SplitTable(tas => tas.Where(y => y.TableName.Contains("12")))//表名包含2019的表
                            .ToList();
                }
            }
        }

        /// <summary>
        /// 按天分表
        /// </summary>
        public static void ShowSubTableDay()
        {
            var connectionconfig = new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true
            };

            #region 初始化数据
            List<CommoditySubTableDay> addList = new List<CommoditySubTableDay>();
            for (int i = 1; i <= 10000; i++)
            {
                int seed = i % 31;
                addList.Add(new CommoditySubTableDay()
                {
                    CategoryId = i,
                    Createtime = DateTime.Now.AddDays(seed),
                    ImageUrl = "",
                    Price = new Random().Next(100, 1000),
                    ProductId = i,
                    Title = $"Title_{i}",
                    Url = $"Url_{i}",
                    Id = Guid.NewGuid()
                });
            }
            #endregion

            using (SqlSugarClient db = new SqlSugarClient(connectionconfig))
            {

                db.CodeFirst
                .SplitTables()//标识分表
                 .InitTables<CommoditySubTableDay>(); //程序启动时加这一行,如果一张表没有会初始化一张

                {
                    // 分表后查询时不需要区分那个表 - 非常智能
                    DateTime beginDate = DateTime.Now;
                    DateTime endDate = DateTime.Now.AddDays(20);
                    var list = db.Queryable<CommoditySubTableDay>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    List<CommoditySubTableDay> delList = db.Queryable<CommoditySubTableDay>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                    db.Deleteable(delList) // 删除表数据
                        .SplitTable()//标识分表
                        .ExecuteCommand();
                }

                long iResult = db.Insertable(addList)
                     .SplitTable()//标识分表
                     .ExecuteCommand();


                {
                    //注意：
                    //1、 分页有 OrderBy写 SplitTable 后面 ,uinon all后在排序
                    //2、 Where尽量写到 SplitTable 前面，先过滤在union all 
                    //原理：(sql union sql2) 写SplitTable 后面生成的括号外面，写前生成的在括号里面

                    DateTime beginDate = DateTime.Now;
                    DateTime endDate = DateTime.Now.AddDays(20);
                    var list = db.Queryable<CommoditySubTableDay>()
                        .SplitTable(beginDate, endDate)//标识分表
                        .ToList();
                }


                {
                    //近3张，也可以表达式选择
                    var list = db.Queryable<CommoditySubTableDay>()
                        .SplitTable(tabs => tabs.Take(3))//标识分表
                        .ToList();
                }
                //确定某一个表名查询
                {
                    var name = db.SplitHelper<CommoditySubTableDay>().GetTableName(DateTime.Now.AddYears(3));//根据时间获取表名
                    var list = db.Queryable<CommoditySubTableDay>()
                        .SplitTable(tas => tas.InTableNames(name))//标识分表
                        .ToList();
                }

                //表名模糊匹配
                {
                    var list = db.Queryable<CommoditySubTableDay>()
                            .SplitTable(tas => tas.Where(y => y.TableName.Contains("12")))//表名包含2019的表
                            .ToList();
                }
            }
        }
    }
}
